package com.svmc.app.task.adapter;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

import com.svmc.app.task.model.HelpModel;
import com.svmc.app.task.model.TaskModel;

public class DbAdapter extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "taskmanager";
	/*
	 * Main table : TaskNote
	 */
	private static final String TABLE_TASK_NAME = "tasknote";

	public static final String ID = "id";
	public static final String TASKNOTE = "task";
	public static final String TIMEFINISH = "timefinish";
	public static final String TIMEFINISHFORSORT = "timeforsort";
	public static final String NOTE = "note";
	public static final String TIMEREMINDER = "timereminder";
	public static final String STATUS = "status";
	public static final String ISREMINDER = "isreminder";
	public static final String IDALARM = "id_alarm";
	public static final String PATH = "path";

	/*
	 * Task help to user
	 */
	private static final String TABLE_HELP = "table_help";

	public static final String HELP_ID = "help_id";
	public static final String HELP_NAME = "help_name";
	public static final String HELP_CONCEPT = "concept";

	public static final String CREATE_TABLE = "CREATE TABLE " + TABLE_TASK_NAME
			+ "(" + ID + " INTEGER PRIMARY KEY," + TASKNOTE + " TEXT,"
			+ TIMEFINISH + " TEXT," + NOTE + " TEXT," + TIMEREMINDER + " TEXT,"
			+ PATH + " TEXT," + ISREMINDER + " INTEGER," + IDALARM
			+ " INTEGER," + STATUS + " INTEGER" + ")";

	public static final String CREAT_HELP_TABLE = "CREATE TABLE " 
			+ TABLE_HELP + "(" 
			+ HELP_ID + " integer primary key," 
			+ HELP_NAME + " text,"
			+ HELP_CONCEPT + " text" + ")";

	public DbAdapter(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_TABLE);
		db.execSQL(CREAT_HELP_TABLE);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE_TASK_NAME IF EXISTS " + TABLE_TASK_NAME);
		db.execSQL("DROP TABLE_HELP IF EXISTS " + TABLE_TASK_NAME);
		onCreate(db);

	}

	/**
	 * add new task
	 * 
	 * @param task
	 * @return row database is add successful ( or it is ID of task)
	 */
	public long addTask(TaskModel task) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(TASKNOTE, task.getTask());
		cv.put(TIMEFINISH, task.getTimefinish());
		cv.put(NOTE, task.getNote());
		cv.put(TIMEREMINDER, task.getTimereminder());
		cv.put(STATUS, task.getStatus());
		cv.put(ISREMINDER, task.getIsreminder());
		cv.put(IDALARM, task.getId_alarm());
		cv.put(PATH, task.getPathRingtone());
		long rowID = db.insert(TABLE_TASK_NAME, null, cv);
		db.close();
		return rowID;
	}

	public TaskModel getTaskById(int id) {
		TaskModel task = new TaskModel();
		SQLiteDatabase db = this.getReadableDatabase();
		String selectquery = "SELECT *FROM " + TABLE_TASK_NAME + " WHERE " + ID
				+ " = " + id;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				task.setId(cursor.getInt(cursor.getColumnIndex(ID)));
				task.setTask(cursor.getString(cursor.getColumnIndex(TASKNOTE)));
				task.setTimefinish(cursor.getString(cursor
						.getColumnIndex(TIMEFINISH)));
				task.setNote(cursor.getString(cursor.getColumnIndex(NOTE)));
				task.setTimereminder(cursor.getString(cursor
						.getColumnIndex(TIMEREMINDER)));
				task.setStatus(cursor.getInt(cursor.getColumnIndex(STATUS)));
				task.setIsreminder(cursor.getInt(cursor
						.getColumnIndex(ISREMINDER)));
				task.setId_alarm(cursor.getInt(cursor.getColumnIndex(IDALARM)));
				task.setPathRingtone(cursor.getString(cursor
						.getColumnIndex(PATH)));
			}
			cursor.close();
		}

		db.close();
		return task;

	}

	public ArrayList<TaskModel> getAllTask() {

		ArrayList<TaskModel> listtask = new ArrayList<TaskModel>();
		SQLiteDatabase db = this.getReadableDatabase();
		String selectquery = "SELECT *FROM " + TABLE_TASK_NAME;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor.moveToFirst()) {
			do {
				TaskModel task = new TaskModel();

				task.setId(cursor.getInt(cursor.getColumnIndex(ID)));
				task.setTask(cursor.getString(cursor.getColumnIndex(TASKNOTE)));
				task.setTimefinish(cursor.getString(cursor
						.getColumnIndex(TIMEFINISH)));
				task.setNote(cursor.getString(cursor.getColumnIndex(NOTE)));
				task.setTimereminder(cursor.getString(cursor
						.getColumnIndex(TIMEREMINDER)));
				task.setStatus(cursor.getInt(cursor.getColumnIndex(STATUS)));
				task.setIsreminder(cursor.getInt(cursor
						.getColumnIndex(ISREMINDER)));
				task.setId_alarm(cursor.getInt(cursor.getColumnIndex(IDALARM)));
				task.setPathRingtone(cursor.getString(cursor
						.getColumnIndex(PATH)));
				listtask.add(task);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return listtask;
	}

	/**
	 * update status of taskmodel
	 * 
	 * @param task
	 * @return row in database is changed ( if return = -1 ,update is not
	 *         success)
	 */
	public int updateStatusTask(TaskModel task) {

		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(ID, task.getId());
		cv.put(TASKNOTE, task.getTask());
		cv.put(TIMEFINISH, task.getTimefinish());
		cv.put(NOTE, task.getNote());
		cv.put(TIMEREMINDER, task.getTimereminder());
		cv.put(PATH, task.getPathRingtone());
		cv.put(ISREMINDER, task.getIsreminder());
		cv.put(IDALARM, task.getId_alarm());
		cv.put(STATUS, task.getStatus());
		int row = db.update(TABLE_TASK_NAME, cv, ID + " = ?",
				new String[] { String.valueOf(task.getId()) });
		db.close();
		return row;

	}

	/**
	 * get all task have status = 1( task is not finish);
	 * 
	 * @param status
	 * @return arraylist < taskmodel>
	 */
	public ArrayList<TaskModel> getAllTaskByStatus(int status) {
		ArrayList<TaskModel> listtask = new ArrayList<TaskModel>();
		// to do something
		SQLiteDatabase db = this.getReadableDatabase();
		String selectquery = "SELECT *FROM " + TABLE_TASK_NAME + " WHERE "
				+ STATUS + " = " + status;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor.moveToFirst()) {
			do {
				TaskModel task = new TaskModel();

				task.setId(cursor.getInt(cursor.getColumnIndex(ID)));
				task.setTask(cursor.getString(cursor.getColumnIndex(TASKNOTE)));
				task.setTimefinish(cursor.getString(cursor
						.getColumnIndex(TIMEFINISH)));
				task.setNote(cursor.getString(cursor.getColumnIndex(NOTE)));
				task.setTimereminder(cursor.getString(cursor
						.getColumnIndex(TIMEREMINDER)));
				task.setStatus(cursor.getInt(cursor.getColumnIndex(STATUS)));
				task.setIsreminder(cursor.getInt(cursor
						.getColumnIndex(ISREMINDER)));
				task.setId_alarm(cursor.getInt(cursor.getColumnIndex(IDALARM)));
				task.setPathRingtone(cursor.getString(cursor
						.getColumnIndex(PATH)));
				listtask.add(task);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return listtask;
	}

	/**
	 * Sort alltast ASC or DESC ,return arraylist taskmode
	 * 
	 * @param type
	 *            (must is : ASC or DESC )
	 * @return
	 */
	public ArrayList<TaskModel> getAllTastSortName(String type) {
		ArrayList<TaskModel> listtask = new ArrayList<TaskModel>();
		SQLiteDatabase db = this.getReadableDatabase();
		String selectquery = "SELECT *FROM " + TABLE_TASK_NAME + " ORDER BY "
				+ TASKNOTE + " " + type;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor.moveToFirst()) {
			do {
				TaskModel task = new TaskModel();

				task.setId(cursor.getInt(cursor.getColumnIndex(ID)));
				task.setTask(cursor.getString(cursor.getColumnIndex(TASKNOTE)));
				task.setTimefinish(cursor.getString(cursor
						.getColumnIndex(TIMEFINISH)));
				task.setNote(cursor.getString(cursor.getColumnIndex(NOTE)));
				task.setTimereminder(cursor.getString(cursor
						.getColumnIndex(TIMEREMINDER)));
				task.setStatus(cursor.getInt(cursor.getColumnIndex(STATUS)));
				task.setIsreminder(cursor.getInt(cursor
						.getColumnIndex(ISREMINDER)));
				task.setId_alarm(cursor.getInt(cursor.getColumnIndex(IDALARM)));
				task.setPathRingtone(cursor.getString(cursor
						.getColumnIndex(PATH)));
				listtask.add(task);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return listtask;
	}

	/**
	 * Sort alltast ASC or DESC by time finish,return arraylist taskmode
	 */
	public ArrayList<TaskModel> getAllTaskSortTime(String type) {
		ArrayList<TaskModel> listtask = new ArrayList<TaskModel>();
		SQLiteDatabase db = this.getReadableDatabase();
		String selectquery = "SELECT *FROM " + TABLE_TASK_NAME + " ORDER BY "
				+ TIMEFINISH + " " + type;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor.moveToFirst()) {
			do {
				TaskModel task = new TaskModel();

				task.setId(cursor.getInt(cursor.getColumnIndex(ID)));
				task.setTask(cursor.getString(cursor.getColumnIndex(TASKNOTE)));
				task.setTimefinish(cursor.getString(cursor
						.getColumnIndex(TIMEFINISH)));
				task.setNote(cursor.getString(cursor.getColumnIndex(NOTE)));
				task.setTimereminder(cursor.getString(cursor
						.getColumnIndex(TIMEREMINDER)));
				task.setStatus(cursor.getInt(cursor.getColumnIndex(STATUS)));
				task.setIsreminder(cursor.getInt(cursor
						.getColumnIndex(ISREMINDER)));
				task.setId_alarm(cursor.getInt(cursor.getColumnIndex(IDALARM)));
				task.setPathRingtone(cursor.getString(cursor
						.getColumnIndex(PATH)));
				listtask.add(task);
			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return listtask;
	}

	public ArrayList<Integer> getAllIdAlarm() {
		ArrayList<Integer> listAlarm = new ArrayList<Integer>();
		SQLiteDatabase db = this.getReadableDatabase();
		String selectquery = "SELECT " + IDALARM + " FROM " + TABLE_TASK_NAME;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor.moveToFirst()) {
			do {
				int idalarm = cursor.getInt(cursor.getColumnIndex(IDALARM));
				if (idalarm != 0) {
					Integer integer = new Integer(idalarm);
					listAlarm.add(integer);
				}

			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return listAlarm;
	}

	/**
	 * delete one task
	 * 
	 * @param task
	 */
	public int deleteTask(TaskModel task) {
		SQLiteDatabase db = this.getWritableDatabase();
		int indexRow = db.delete(TABLE_TASK_NAME, ID + " =?",
				new String[] { String.valueOf(task.getId()) });
		db.close();
		return indexRow;
	}

	/**
	 * delete one task when know a ID of task
	 * 
	 * @param id
	 * @return
	 */
	public int deleteTaskByID(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		int indexRow = db.delete(TABLE_TASK_NAME, ID + " =?",
				new String[] { String.valueOf(id) });
		db.close();
		return indexRow;
	}

	/**
	 * Delete all task from database
	 */
	public void deteleAllTask() {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_TASK_NAME, null, null);
		db.close();
	}
	/**
	 * add new TIP to database
	 * @param help
	 * @return
	 */
	public long addHelp(HelpModel help){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();
		cv.put(HELP_NAME, help.getNameTip());
		cv.put(HELP_CONCEPT, help.getConceptTip());
		long rowID = db.insert(TABLE_HELP, null, cv);
		db.close();
		return rowID;
		
	}
	/**
	 * Get all Help
	 * @return
	 */
	public ArrayList<HelpModel> getAllHelp (){
		ArrayList<HelpModel> listHelp = new ArrayList<HelpModel>();
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = "SELECT *FROM " + TABLE_HELP;
		Cursor cursor = db.rawQuery(selectQuery, null);
		if(cursor.moveToFirst()){
			do {
				HelpModel help = new HelpModel();
				help.setId(cursor.getInt(cursor.getColumnIndex(HELP_ID)));
				help.setNameTip(cursor.getString(cursor.getColumnIndex(HELP_NAME)));
				help.setConceptTip(cursor.getString(cursor.getColumnIndex(HELP_CONCEPT)));
				listHelp.add(help);
			}while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return listHelp;
	}
	public HelpModel getDetailHelpByID(int id){
		HelpModel help = new HelpModel();
		SQLiteDatabase db = this.getReadableDatabase();
		String selectquery = "SELECT *FROM " + TABLE_HELP + " WHERE " + HELP_ID
				+ " = " + id;
		Cursor cursor = db.rawQuery(selectquery, null);
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				help.setId(cursor.getInt(cursor.getColumnIndex(HELP_ID)));
				help.setNameTip(cursor.getString(cursor.getColumnIndex(HELP_NAME)));
				help.setConceptTip(cursor.getString(cursor.getColumnIndex(HELP_CONCEPT)));
			}
			cursor.close();
		}
		
		db.close();
		return help;
	}
	
	

	public void show(Context mcontext) {
		Toast.makeText(mcontext, "okie men", 0).show();
	}
}
